from openpyxl import load_workbook
import calendar
def get_data():
    """
    读取图书数据分析汇总表格
    :return:字典，key-value,key是sku，value是每一天的数据
    """
    ws = wb.active
    data = {}
    for number in range(2,ws.max_row+1):
        content = {}
        sku = ws.cell(row=number,column=3).value # 获取sku
        sku = str(sku)
        content['date'] = ws.cell(row=number,column=1).value # 获取日期
        content['name'] = ws.cell(row=number,column=2).value # 获取名字
        content['view_count'] = ws.cell(row=number,column=8).value # 获取浏览量
        content['visitor_count'] = ws.cell(row=number,column=9).value # 获取浏览人数
        content['deal_quantity'] = ws.cell(row=number,column=12).value #成交数量
        content['deal_amount'] = ws.cell(row=number,column=15).value #成交金额
        content['additional_deal'] = ws.cell(row=number,column=16).value #加购人数
        #判断字典中是否已经存在这个key（sku），如果不存在设置data【sku】，然后追加
        if sku not in data.keys():
            data[sku] = []
        data[sku].append(content) # 追加到列表
    return data


def create_view_count_sheet(data):
    # 设置表头
    header = ['序号', '商品编号', '商品名称', '1', '2', '3', '4', '5', '6', '7', '8', '9', '10', '11', '12', '13', '14', '15', '16', '17', '18', '19', '20', '21', '22', '23', '24', '25', '26', '27', '28', '29', '30', '31', '合计', '均值']
    ws = wb.create_sheet('浏览量', 0)
    ws.append(header)
    for number,temp in enumerate(data.items()):
        row = number+2
        sku,books = temp
        ws.cell(row=row,column=1).value = number + 1 # index
        ws.cell(row=row,column=2).value = sku  #sku
        ws.cell(row=row,column=3).value = books[0]['name']  # 图书名称
        for book in books:
            day = book['date'].day
            ws.cell(row=row,column=3+day).value = book['view_count']
        #计算总数
        ws.cell(row=row,column=35).value = f"=SUM(D{row}:AH{row})"
        #计算均值
        ws.cell(row=row,column=36).value = f"=ROUND(AVERAGE(D{row}:AH{row}),2)"

def create_visitor_count_sheet(data):
    # 设置表头
    header = ['序号', '商品编号', '商品名称', '1', '2', '3', '4', '5', '6', '7', '8', '9', '10', '11', '12', '13', '14',
              '15', '16', '17', '18', '19', '20', '21', '22', '23', '24', '25', '26', '27', '28', '29', '30', '31',
              '合计', '均值']
    ws = wb.create_sheet('访客数', 1)
    ws.append(header) # 写入表头
    for number, temp in enumerate(data.items()):
        row = number + 2
        sku, books = temp
        ws.cell(row=row, column=1).value = number + 1  # index
        ws.cell(row=row, column=2).value = sku  # sku
        ws.cell(row=row, column=3).value = books[0]['name']  # 图书名称
        for book in books:
            day = book['date'].day
            ws.cell(row=row, column=3 + day).value = book['visitor_count']
        # 计算总数
        ws.cell(row=row, column=35).value = f"=SUM(D{row}:AH{row})"
        # 计算均值
        ws.cell(row=row, column=36).value = f"=ROUND(AVERAGE(D{row}:AH{row}),2)"

def create_additional_deal_sheet(data):
    """
    创建加购人数
    :param data: 字典类型数据，包含每天的商品销量
    :return: none
    """
    # 设置表头
    header = ['序号', '商品编号', '商品名称', '1', '2', '3', '4', '5', '6', '7', '8', '9', '10', '11', '12', '13', '14',
              '15', '16', '17', '18', '19', '20', '21', '22', '23', '24', '25', '26', '27', '28', '29', '30', '31',
              '合计', '均值']
    ws = wb.create_sheet('加购人数', 2)
    ws.append(header)  # 写入表头
    for number, temp in enumerate(data.items()):
        row = number + 2
        sku, books = temp
        ws.cell(row=row, column=1).value = number + 1  # index
        ws.cell(row=row, column=2).value = sku  # sku
        ws.cell(row=row, column=3).value = books[0]['name']  # 图书名称
        for book in books:
            day = book['date'].day
            ws.cell(row=row, column=3 + day).value = book['additional_deal']
        # 计算总数
        ws.cell(row=row, column=35).value = f"=SUM(D{row}:AH{row})"
        # 计算均值
        ws.cell(row=row, column=36).value = f"=INT(AVERAGE(D{row}:AH{row}))"

def create_additional_deal_rate_sheet(data):
    """加购率"""
    # 设置表头
    header = ['序号', '商品编号', '商品名称', '1', '2', '3', '4', '5', '6', '7', '8', '9', '10', '11', '12', '13', '14',
              '15', '16', '17', '18', '19', '20', '21', '22', '23', '24', '25', '26', '27', '28', '29', '30', '31',
             '均值']
    ws = wb.create_sheet('加购率', 3)
    ws.append(header)  # 写入表头
    for number, temp in enumerate(data.items()):
        row = number + 2
        sku, books = temp
        ws.cell(row=row, column=1).value = number + 1  # index
        ws.cell(row=row, column=2).value = sku  # sku
        ws.cell(row=row, column=3).value = books[0]['name']  # 图书名称
        for book in books:
            day = book['date'].day
            ws.cell(row=row, column=3 + day).value = book['additional_deal'] / book['visitor_count']
            ws.cell(row=row, column=3 + day).number_format = '0.00%' # 设置百分比格式

        # 计算均值
        ws.cell(row=row, column=35).value = f"=AVERAGE(D{row}:AH{row})"
        ws.cell(row=row, column=35).number_format = '0.00%'

def create_deal_quantity_sheet(data):
    """成交数"""
    # 设置表头
    header = ['序号', '商品编号', '商品名称', '1', '2', '3', '4', '5', '6', '7', '8', '9', '10', '11', '12', '13', '14',
              '15', '16', '17', '18', '19', '20', '21', '22', '23', '24', '25', '26', '27', '28', '29', '30', '31',
              '合计', '均值']
    ws = wb.create_sheet('成交数', 4)
    ws.append(header)  # 写入表头
    for number, temp in enumerate(data.items()):
        row = number + 2
        sku, books = temp
        ws.cell(row=row, column=1).value = number + 1  # index
        ws.cell(row=row, column=2).value = sku  # sku
        ws.cell(row=row, column=3).value = books[0]['name']  # 图书名称
        for book in books:
            day = book['date'].day
            ws.cell(row=row, column=3 + day).value = book['deal_quantity']
        # 计算总数
        ws.cell(row=row, column=35).value = f"=SUM(D{row}:AH{row})"
        # 计算均值
        ws.cell(row=row, column=36).value = f"=ROUND(AVERAGE(D{row}:AH{row}),2)"
def create_deal_quantity_rate_sheet(data):
    """成交率"""
    # 设置表头
    header = ['序号', '商品编号', '商品名称', '1', '2', '3', '4', '5', '6', '7', '8', '9', '10', '11', '12', '13', '14',
              '15', '16', '17', '18', '19', '20', '21', '22', '23', '24', '25', '26', '27', '28', '29', '30', '31',
              '均值']
    ws = wb.create_sheet('成交率', 5)
    ws.append(header)  # 写入表头
    for number, temp in enumerate(data.items()):
        row = number + 2
        sku, books = temp
        ws.cell(row=row, column=1).value = number + 1  # index
        ws.cell(row=row, column=2).value = sku  # sku
        ws.cell(row=row, column=3).value = books[0]['name']  # 图书名称
        for book in books:
            day = book['date'].day
            ws.cell(row=row, column=3 + day).value = book['deal_quantity'] / book['visitor_count']
            ws.cell(row=row, column=3 + day).number_format = '0.00%'  # 设置百分比格式

        # 计算均值
        ws.cell(row=row, column=35).value = f"=AVERAGE(D{row}:AH{row})"
        ws.cell(row=row, column=35).number_format = '0.00%'

def get_last_month_data():
    """获取上个月数据"""
    wb = load_workbook('../data/Excel大屏数据/2021年6月图书销售任务.xlsx')
    ws = wb['销售任务']
    data = {}
    for row in ws.iter_rows(min_row=3):
        sku = str(row[0].value) # 获取sku
        data[sku] = {
            'additonal_quantity' : row[2].value,
            'additonal_quantity_rate' : row[3].value,
            'deal_quantity' : row[4].value,
            'deal_rate' : row[5].value,
            'deal_amount' : row[6].value,
            'target' : row[7].value,
        }
    return data

def create_deal_mount_sheet(data,last_month_data):
    """生成成交金额"""
    # 设置表头
    header = ['序号', '商品编号', '商品名称', '1', '2', '3', '4', '5', '6', '7', '8', '9', '10', '11', '12', '13', '14',
              '15', '16', '17', '18', '19', '20', '21', '22', '23', '24', '25', '26', '27', '28', '29', '30', '31',
              '合计', '均值','5月总额','5月均值','均值变化','6月份目标']
    ws = wb.create_sheet('成交金额', 6)
    ws.append(header)
    for number, temp in enumerate(data.items()):
        row = number + 2
        sku, books = temp
        ws.cell(row=row, column=1).value = number + 1  # index
        ws.cell(row=row, column=2).value = sku  # sku
        ws.cell(row=row, column=3).value = books[0]['name']  # 图书名称
        sum = 0 #初始化sum
        for book in books:
            day = book['date'].day
            ws.cell(row=row, column=3 + day).value = book['deal_amount']
            sum += book['deal_amount']
        # 计算总数
        ws.cell(row=row, column=35).value = sum
        # 计算均值
        ws.cell(row=row, column=36).value = sum / get_month_days(current_month)
        current_month_average = sum / get_month_days(current_month)
        #5月总额
        ws.cell(row=row,column=37).value = last_month_data[sku]['deal_amount']
        #5月均值
        last_month_average = last_month_data[sku]['deal_amount'] / get_month_days(current_month - 1)
        ws.cell(row=row, column=38).value = f"{last_month_average: .2f}"
        #均值变化
        ws.cell(row=row, column=39).value = f"{current_month_average - last_month_average : .2f}"
        #本月目标
        ws.cell(row=row, column=40).value = last_month_data[sku]['target']

def get_month_days(month):
    """获取month月份天数"""
    return calendar.mdays[month]

if __name__ == '__main__':
    current_month = 6 #本月
    wb = load_workbook('../data/Excel大屏数据/2021年6月图书数据分析汇总.xlsx')
    data = get_data() # 获取exc原始数据
    create_view_count_sheet(data) #生成浏览量
    create_visitor_count_sheet(data) #生成访客数
    create_additional_deal_sheet(data)#生成加购人数
    create_additional_deal_rate_sheet(data) #生成加购率
    create_deal_quantity_sheet(data) # 生成成交数
    create_deal_quantity_rate_sheet(data) # 生成成交率
    last_month_data = get_last_month_data()  # 获取上个月数据
    create_deal_mount_sheet(data,last_month_data) #生成成交金额sheet
    wb.close()
    wb.save('数据汇总.xlsx')